using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;

// 数据模型类
public class ExcelDataModel
{
    public string ColumnA { get; set; }
    public string ColumnB { get; set; }
    public string ColumnC { get; set; }
    public string ColumnD { get; set; }
}

public class ExcelReader
{
    public List<ExcelDataModel> ReadExcel(string filePath)
    {
        // 1. 判断文件扩展名
        using var fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
        IWorkbook workbook = Path.GetExtension(filePath).ToLower() switch
        {
            ".xls" => new HSSFWorkbook(fs),
            ".xlsx" => new XSSFWorkbook(fs),
            _ => throw new NotSupportedException("不支持的Excel文件格式")
        };

        var sheet = workbook.GetSheetAt(0);
        var dataList = new List<ExcelDataModel>();

        // 2. 读取前四行数据
        for (int rowIndex = 0; rowIndex < 4 && rowIndex <= sheet.LastRowNum; rowIndex++)
        {
            var row = sheet.GetRow(rowIndex);
            if (row == null) continue;

            // 3. 验证D列数据
            var columnD = GetCellValue(row.GetCell(3)); // D列索引为3
            if (!IsValidColumnD(columnD))
            {
                throw new InvalidDataException($"第 {rowIndex + 1} 行D列数据不符合要求");
            }

            // 4. 创建数据模型
            var model = new ExcelDataModel
            {
                ColumnA = GetCellValue(row.GetCell(0)), // A列
                ColumnB = GetCellValue(row.GetCell(1)), // B列
                ColumnC = GetCellValue(row.GetCell(2)), // C列
                ColumnD = columnD
            };

            dataList.Add(model);
        }

        return dataList;
    }

    private string GetCellValue(ICell cell)
    {
        if (cell == null) return null;

        return cell.CellType switch
        {
            CellType.String => cell.StringCellValue,
            CellType.Numeric => cell.NumericCellValue.ToString(),
            CellType.Boolean => cell.BooleanCellValue.ToString(),
            CellType.Formula => GetFormulaValue(cell),
            _ => null
        };
    }

    private string GetFormulaValue(ICell cell)
    {
        try
        {
            return cell.NumericCellValue.ToString();
        }
        catch
        {
            return cell.StringCellValue;
        }
    }

    private bool IsValidColumnD(string value)
    {
        // 3. 验证D列：长度10且全为数字
        return !string.IsNullOrWhiteSpace(value) &&
               value.Length == 10 &&
               value.All(char.IsDigit);
    }
}

// 使用示例
var reader = new ExcelReader();
try
{
    var data = reader.ReadExcel("data.xlsx");
    Console.WriteLine($"成功读取 {data.Count} 条数据");
    foreach (var item in data)
    {
        Console.WriteLine($"A: {item.ColumnA}, B: {item.ColumnB}, C: {item.ColumnC}, D: {item.ColumnD}");
    }
}
catch (Exception ex)
{
    Console.WriteLine($"错误: {ex.Message}");
}